﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.Common;
using DBLGen.UI.Design.Query.DBAdapter;

namespace DBLGen.UI.Design.Query
{
	public partial class QueryControl : UserControl
	{
		public string SqlQuery = string.Empty;
		public string SqlQueryPreview = string.Empty;
		public ColumnCollection ColumnList = new ColumnCollection();
		public List<QueryRow> QueryRows = new List<QueryRow>();

		#region Methods

		public QueryControl()
		{
			InitializeComponent();
		}

		public void BuildSQL()
		{
			this.BuildQueryRowList();
			this.PrintDisplaySql(true);
			this.PrintDisplaySql(false);
		}

		private void BuildQueryRowList()
		{
			this.QueryRows.Clear();

			QueryRow row;

			for (int j = 0; j < this.dataGridView1.Rows.Count - 1; j++)
			{
				DataGridViewRow dgvr = this.dataGridView1.Rows[j];
				if (dgvr != null)
				{
					row = this.ParseRow(j, dgvr);
					if(row!=null)
						this.QueryRows.Add(row);
				}
			}
		}

		private QueryRow ParseRow(int index, DataGridViewRow row)
		{
			#region CheckRow
			if (!this.CheckRow(row))
			{
				MessageBox.Show("Ensure your selection, system cannot create query!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return null;
			}
			#endregion CheckRow

			string conjunction = row.Cells[0].Value.ToString();
			string field = row.Cells[1].Value.ToString();
			string operandText = row.Cells[2].Value.ToString();
			Operand operand = (Operand)Enum.Parse(typeof(Operand), operandText);
			string sqlOperand = Query.SqlOperand.GetSQLOperand(operand);
			string parameterName = row.Cells[3].Value.ToString();
			string testParameterValue = row.Cells[4].Value == null ? string.Empty : row.Cells[4].Value.ToString();

			return new QueryRow(index, field, operandText, conjunction, parameterName, testParameterValue);
		}

		private void PrintDisplaySql(bool display)
		{
			string tblName = ColumnList[0].Table.Name;

			StringBuilder sb = new StringBuilder();
			sb.Append(@"SELECT ");
			
			#region ColumnList
			for (int i = 0; i < this.ColumnList.Count; i++)
			{
				sb.AppendFormat(@" [{0}]", this.ColumnList[i].Name);

				if (i < this.ColumnList.Count - 1)
				{
					sb.Append(@", ");
				}

				if (i % 3 == 0)
				{
					sb.Append(Environment.NewLine);
				}
			}
			#endregion ColumnList

			sb.AppendFormat(Environment.NewLine + @"FROM [{0}] " + Environment.NewLine, tblName);

			bool first = true;
			foreach (QueryRow row in this.QueryRows)
			{
				#region check first
				if (first)
				{
					sb.Append(@"WHERE ");
					first = false;
				}
				#endregion check first

				string colType = this.GetColumnType(row.FieldName);
				string testValue = row.DefaultValue;
				
				#region col type handling
				switch (colType)
				{
					case "bool":
					case "System.Boolean":
						testValue = bool.Parse(testValue) == true ? "1" : "0";
						break;
					case "DateTime":
					case "System.DateTime":
						DateTime dt;
						testValue = DateTime.TryParse(testValue, out dt) == true ?
							"'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'":
							"'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'";
						break;
					case "Guid":
					case "System.Guid":
						testValue = "'" + (string.IsNullOrEmpty(testValue) ? Guid.NewGuid().ToString() : testValue) + "'";
						break;
						
				}
				#endregion col type handling

				List<string> param = new List<string>();
				param.Add(testValue);

				string result = this.GetOperandSQL(display, row, param.ToArray());
				#region Conjunction
				if (row.Conjunction.Equals(" -- "))
				{
					sb.AppendFormat(@"( {0} )" + Environment.NewLine, result);
				}
				else if (row.Conjunction.Equals(" OR "))
				{
					sb.AppendFormat(@"OR ( {0} )" + Environment.NewLine, result);
				}
				else
				{
					sb.AppendFormat(@"AND ( {0} )" + Environment.NewLine, result);
				}
				#endregion
			}

			if(!display)
				this.SqlQueryPreview = sb.ToString();
			else
				this.SqlQuery = sb.ToString();
		}

		private string GetOperandSQL(bool previewInEditor, QueryRow row, params string[] value)
		{
			string result = string.Empty;
			if(value != null && value.Length > 0)
			{
				string val1 = value[0];
				string val2 = string.Empty;
				if (value.Length > 1)
					val2 = value[1];

				switch (row.Operand)
				{
					case Operand.Between:
						result += row.FieldName + " BETWEEN '" + (previewInEditor == true ? row.ParameterName : val1);
						result += "' AND '" + (previewInEditor == true ? row.ParameterName : val2) + "'";
						break;
					case Operand.In:
						result += row.FieldName + " IN (" + (previewInEditor == true ? row.ParameterName : this.Splitter(val1)) + ") ";
						break;
					case Operand.NotIn:
						result += row.FieldName + " NOT IN (" + (previewInEditor == true ? row.ParameterName : this.Splitter(val1)) + ") ";
						break;
					default:
						{
							result += row.FieldName + " " + SqlOperand.GetSQLOperand(row.Operand) + " " + (previewInEditor == true ? row.ParameterName : val1) ;
							break;
						}
				}

				if (string.IsNullOrEmpty(result))
				{
					result = val1;
				}
			}
			return result;
		}

		private string Splitter(string value)
		{
			string result = string.Empty;
			if (!string.IsNullOrEmpty(value))
			{
				char[] keys = new char[] { '\'', ',', ';', ':', '|', ':', '/', '\\', '-', '+', '#', '$', '%', '&', '*' };

				string[] items = value.Split(keys);
				List<string> cleanEmpty = new List<string>();
				// cleanup all empty items
				foreach (string item in items)
				{
					if (!string.IsNullOrEmpty(item))
						cleanEmpty.Add(item);
				}
				for (int i = 0; i < cleanEmpty.Count; i++)
				{
					result += cleanEmpty[i].Trim().ToString();
					if (i < cleanEmpty.Count - 1)
					{
						result += ", ";
					}
				}
			}
			return result;
		}

		private string GetColumnType(string columnName)
		{
			for (int i = 0; i < this.ColumnList.Count; i++)
			{
				if (this.ColumnList[i].Name.Equals(columnName))
				{
					return this.ColumnList[i].Type;
				}
			}
			return string.Empty;
		}

		private bool CheckRow(DataGridViewRow row)
		{
			bool result = true;
			if (row.Cells[0] == null && row.Cells[0].Value == null) return false;
			if (row.Cells[1] == null && row.Cells[1].Value == null) return false;
			if (row.Cells[2] == null && row.Cells[2].Value == null) return false;
			if (row.Cells[3] == null && row.Cells[3].Value == null) return false;
			if (row.Cells[4] == null && row.Cells[4].Value == null) return false;
			
			return result;
		}
		
		private void SwitchOverCol(int row, int col)
		{
			switch (col)
			{
				case 0:
					{
						DataGridViewComboBoxCell cmb = new DataGridViewComboBoxCell();
						this.dataGridView1.Rows[row].Cells[col] = cmb;
						this.dataGridView1.Rows[row].Cells[col].ReadOnly = false;
						this.dataGridView1.ReadOnly = false;
						cmb.Items.Clear();
						cmb.Items.Add(" AND ");
						cmb.Items.Add(" OR ");
						if (row == 0)
						{
							cmb.Items.Clear();
							cmb.Items.Add(" -- ");
						}

						break;
					}
				case 1:
					{
						// check first And/Or condition
						if (!this.CheckAndOrCondition(row))
							return;

						DataGridViewComboBoxCell cmb = new DataGridViewComboBoxCell();
						this.dataGridView1.Rows[row].Cells[col] = cmb;
						this.dataGridView1.Rows[row].Cells[col].ReadOnly = false;
						this.dataGridView1.ReadOnly = false;

						cmb.Items.Clear();
						foreach (Column item in this.ColumnList)
						{
							cmb.Items.Add(item.Name);
						}
						break;
					}
				case 2:
					{
						// check first And/Or condition
						if (!this.CheckAndOrCondition(row))
							return;

						Column column = null;
						if (this.dataGridView1.Rows[row].Cells[1].Value != null)
						{
							foreach (Column item in this.ColumnList)
							{
								if (item.Name.Equals(this.dataGridView1.Rows[row].Cells[1].Value.ToString()))
								{
									column = item;
									break;
								}
							}
						}
						else
						{
							MessageBox.Show("Select first your requested column", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
							return;
						}

						DataGridViewComboBoxCell cmb = new DataGridViewComboBoxCell();
						this.dataGridView1.Rows[row].Cells[col] = cmb;
						this.dataGridView1.Rows[row].Cells[col].ReadOnly = false;
						this.dataGridView1.ReadOnly = false;
						// add all options 
						cmb.Items.AddRange(Enum.GetNames(typeof(Operand)));
						

						this.SetQueryParameter(row, column.Name);

						#region Set Sample Value Cell
						switch (column.Type)
						{
							case "int":
							case "long":
							case "decimal":
							case "double":
							case "float":
							case "short":
								{
									DataGridViewTextBoxCell txt = new DataGridViewTextBoxCell();
									this.dataGridView1.Rows[row].Cells[4] = txt;
									txt.Value = string.Empty;
									break;
								}
							case "DateTime":
								{									
									CalendarCell cal = new CalendarCell();
									
									this.dataGridView1.Rows[row].Cells[4] = cal;
									cal.Value = DateTime.Now;
									break;
								}
							case "bool":
								{
									DataGridViewCheckBoxCell checkBox = new DataGridViewCheckBoxCell();
									checkBox.Value = true;
									this.dataGridView1.Rows[row].Cells[4] = checkBox;
									this.dataGridView1.Rows[row].Cells[4].ReadOnly = false;
									break;
								}
							default:

								break;
						}
						#endregion


						break;
					}
			}
		}

		private bool CheckAndOrCondition(int row)
		{
			if (this.dataGridView1.Rows[row].Cells[0] != null &&
				this.dataGridView1.Rows[row].Cells[0].Value != null &&
				!string.IsNullOrEmpty(this.dataGridView1.Rows[row].Cells[0].Value.ToString()))
			{
				return true;
			}

			MessageBox.Show("Define first aggregation ['And / Or'] column for your new parameter", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			return false;

		}

		private string GetNewParameterName(string parameterName)
		{
			int i = 0;
			string name = string.Empty;
			bool result = false;
			do
			{
				i++;
				name = parameterName + i;
				result = this.IsAvailable(name);
			}
			while (result);

			return name;
		}

		private bool IsAvailable(string parameterName)
		{
			foreach (DataGridViewRow row in this.dataGridView1.Rows)
			{
				if (row.Cells[3].Value != null && !string.IsNullOrEmpty(row.Cells[3].Value.ToString()))
				{
					if (row.Cells[3].Value.ToString().Equals(parameterName))
					{
						return true;
					}
				}
			}
			return false;
		}

		private void SetQueryParameter(int row, string parameterName)
		{
			DataGridViewTextBoxCell txt = new DataGridViewTextBoxCell();
			this.dataGridView1.Rows[row].Cells[3] = txt;
			txt.Value = GetNewParameterName("@" + parameterName);
			this.dataGridView1.Rows[row].Cells[3].ReadOnly = true;
		}

		#endregion

		#region Events
		private void OnCellClick(object sender, DataGridViewCellEventArgs e)
		{
			this.SwitchOverCol(e.RowIndex, e.ColumnIndex);
		}

		protected override void OnLoad(EventArgs e)
		{
			this.dataGridView1.Visible = true;
			this.dataGridView1.EditMode = DataGridViewEditMode.EditOnEnter | DataGridViewEditMode.EditOnF2;

			// adding data grid columns
			this.dataGridView1.Columns.Add("AndOr", "And/Or");
			this.dataGridView1.Columns.Add("Field", "Filed");
			this.dataGridView1.Columns.Add("Operator", "Operator");
			this.dataGridView1.Columns.Add("Parameter", "Parameter");
			this.dataGridView1.Columns.Add("Value", "Value");

			base.OnLoad(e);
		}

		public void SetQueryRows()
		{			
			// maybe we need first to sort items....
			for (int i = 0; i < this.QueryRows.Count; ++i)
			{
				List<object> rowItem = new List<object>();
				rowItem.Add(this.QueryRows[i].Conjunction);
				rowItem.Add(this.QueryRows[i].FieldName);
				rowItem.Add(this.QueryRows[i].Operation);
				rowItem.Add(this.QueryRows[i].ParameterName);
				rowItem.Add(this.QueryRows[i].DefaultValue);
				this.dataGridView1.Rows.Add(rowItem.ToArray());
			}
			
			//for (int j = 0; j < this.dataGridView1.Rows.Count -1; j++)
			//{				
			//  DataGridViewRow row = this.dataGridView1.Rows[j];
			//}
		}
		#endregion Events

	}
}
